package com.blog.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.blog.BO.UserBO;
import com.blog.dao.BaseDao;
import com.blog.dao.UserDao;
import com.blog.entity.User;

public class UserDaoImpl implements UserDao {

	@Override
	public List<User> selectUser(UserBO userBO, int pageNum, int pageSize) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<User> userList = new ArrayList<User>();
		List<Object>  params = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("SELECT * FROM user where 1 = 1");
		if(userBO != null) {
			//查询条件
			if(userBO.getUserId() != 0) {
				sql.append(" AND userId = ?");
				params.add(userBO.getUserId());
			}//用户id
			if(userBO.getUserName()!=null && !"".equals(userBO.getUserName())) {
				sql.append(" AND userName LIKE concat('%',?,'%')");
				params.add(userBO.getUserName());
			}//用户名模糊查询
			if(userBO.getUserPass()!=null && !"".equals(userBO.getUserPass())) {
				sql.append(" AND userPass LIKE concat('%',?,'%')");
				params.add(userBO.getUserPass());
			}//密码模糊查询
			if(userBO.getUserSex()!=null && !"".equals(userBO.getUserSex())) {
				sql.append(" AND userSex = ?");
				params.add(userBO.getUserSex());
			}//性别
			if(userBO.getUserBirth()!=null) {
				sql.append(" AND userBirth = ?");
				params.add(userBO.getUserBirth());
			}//用户生日
			if(userBO.getUserStartBirth()!=null) {
				sql.append(" AND userBirth >= ?");
				params.add(userBO.getUserStartBirth());
			}//用户查询开始生日日期
			if(userBO.getUserEndBirth()!=null) {
				sql.append(" AND userBirth <= ?");
				params.add(userBO.getUserEndBirth());
			}//用户查询结束生日日期
			if(userBO.getUserPhone()!=null && !"".equals(userBO.getUserPhone())) {
				sql.append(" AND userPhone LIKE concat('%',?,'%')");
				params.add(userBO.getUserPhone());
			}//用户电话模糊查询
			if(userBO.getUserEmail()!=null && !"".equals(userBO.getUserEmail())) {
				sql.append(" AND userEmail LIKE concat('%',?,'%')");
				params.add(userBO.getUserEmail());
			}//用户邮箱模糊查询
			if(userBO.getIndividualResume()!=null && !"".equals(userBO.getIndividualResume())) {
				sql.append(" AND individualResume LIKE concat('%',?,'%')");
				params.add(userBO.getIndividualResume());
			}//用户签名模糊查询
			if(userBO.getUserHeadImg()!=null && !"".equals(userBO.getUserHeadImg())) {
				sql.append(" AND userHeadImg LIKE concat('%',?,'%')");
				params.add(userBO.getUserHeadImg());
			}//用户头像模糊查询
			if(userBO.getExperience() != 0) {
				sql.append(" AND experience = ?");
				params.add(userBO.getExperience());
			}//用户经验
			if(userBO.getUserState() != 0) {
				sql.append(" AND userState = ?");
				params.add(userBO.getUserState());
			}//用户状态
			if(userBO.getRegDate()!=null) {
				sql.append(" AND regDate = ?");
				params.add(userBO.getRegDate());
			}//用户注册时间
			if(userBO.getRegStartDate()!=null) {
				sql.append(" AND regDate >= ?");
				params.add(userBO.getRegStartDate());
			}//用户注册时间从什么时间开始，包含输入的时间
			if(userBO.getRegEndDate()!=null) {
				sql.append(" AND regDate <= ?");
				params.add(userBO.getRegEndDate());
			}//用户注册时间从什么时候结束，包含输入的时间
		}
		sql.append(" LIMIT ?,?");
		User selectUser = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql.toString());
			for(int i =0;i<params.size();i++) {
				ps.setObject(i+1, params.get(i));
			}
			ps.setInt(params.size()+1, (pageNum-1)*pageSize);
			ps.setInt(params.size()+2, pageSize);
			res = ps.executeQuery();
			while(res.next()) {
				selectUser = new User();
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				selectUser.setUserId(res.getInt("userId"));
				selectUser.setUserName(res.getString("userName"));
				selectUser.setUserPass(res.getString("userPass"));
				selectUser.setUserSex(res.getString("userSex"));
				Date dt1 = null;
				try {
					dt1 = format.parse(format.format(res.getTimestamp("userBirth")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setUserBirth(dt1);
				selectUser.setUserPhone(res.getString("userPhone"));
				selectUser.setUserEmail(res.getString("userEmail"));
				selectUser.setIndividualResume(res.getString("individualResume"));
				selectUser.setUserHeadImg(res.getString("userHeadImg"));
				selectUser.setExperience(res.getInt("experience"));
				selectUser.setUserState(res.getInt("userState"));
				Date dt2 = null;
				try {
					dt2 = format.parse(format.format(res.getTimestamp("regDate")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setRegDate(dt2);
				userList.add(selectUser);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return userList;
	}

	@Override
	public int selectUserNum() {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		String sql = "SELECT COUNT(1) AS userNum FROM user;";
		int userNum = 0;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				userNum = res.getInt("userNum");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return userNum;
	}

	@Override
	public User selectUserByUserNameAndUserPass(String userName,String userPass) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		String sql = "SELECT * FROM user WHERE userName = ? AND userPass = ?;";
		User selectUser = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1,userName);
			ps.setString(2,userPass);
			res = ps.executeQuery();
			if(res.next()) {
				selectUser = new User();
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				selectUser.setUserId(res.getInt("userId"));
				selectUser.setUserName(res.getString("userName"));
				selectUser.setUserPass(res.getString("userPass"));
				selectUser.setUserSex(res.getString("userSex"));
				Date dt1 = null;
				try {
					dt1 = format.parse(format.format(res.getTimestamp("userBirth")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setUserBirth(dt1);
				selectUser.setUserPhone(res.getString("userPhone"));
				selectUser.setUserEmail(res.getString("userEmail"));
				selectUser.setIndividualResume(res.getString("individualResume"));
				selectUser.setUserHeadImg(res.getString("userHeadImg"));
				selectUser.setExperience(res.getInt("experience"));
				selectUser.setUserState(res.getInt("userState"));
				Date dt2 = null;
				try {
					dt2 = format.parse(format.format(res.getTimestamp("regDate")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setRegDate(dt2);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return selectUser;
	}

	@Override
	public User selectUserByUserId(int userId) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		String sql = "SELECT * FROM user WHERE userId = ?;";
		User selectUser = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,userId);
			res = ps.executeQuery();
			if(res.next()) {
				selectUser = new User();
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				selectUser.setUserId(res.getInt("userId"));
				selectUser.setUserName(res.getString("userName"));
				selectUser.setUserPass(res.getString("userPass"));
				selectUser.setUserSex(res.getString("userSex"));
				Date dt1 = null;
				try {
					dt1 = format.parse(format.format(res.getTimestamp("userBirth")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setUserBirth(dt1);
				selectUser.setUserPhone(res.getString("userPhone"));
				selectUser.setUserEmail(res.getString("userEmail"));
				selectUser.setIndividualResume(res.getString("individualResume"));
				selectUser.setUserHeadImg(res.getString("userHeadImg"));
				selectUser.setExperience(res.getInt("experience"));
				selectUser.setUserState(res.getInt("userState"));
				Date dt2 = null;
				try {
					dt2 = format.parse(format.format(res.getTimestamp("regDate")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setRegDate(dt2);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return selectUser;
	}

	@Override
	public User selectUserByUserName(String userName) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		String sql = "SELECT * FROM user WHERE userName = ?;";
		User selectUser = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1,userName);
			res = ps.executeQuery();
			if(res.next()) {
				selectUser = new User();
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				selectUser.setUserId(res.getInt("userId"));
				selectUser.setUserName(res.getString("userName"));
				selectUser.setUserPass(res.getString("userPass"));
				selectUser.setUserSex(res.getString("userSex"));
				Date dt1 = null;
				try {
					dt1 = format.parse(format.format(res.getTimestamp("userBirth")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setUserBirth(dt1);
				selectUser.setUserPhone(res.getString("userPhone"));
				selectUser.setUserEmail(res.getString("userEmail"));
				selectUser.setIndividualResume(res.getString("individualResume"));
				selectUser.setUserHeadImg(res.getString("userHeadImg"));
				selectUser.setExperience(res.getInt("experience"));
				selectUser.setUserState(res.getInt("userState"));
				Date dt2 = null;
				try {
					dt2 = format.parse(format.format(res.getTimestamp("regDate")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setRegDate(dt2);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return selectUser;
	}

	@Override
	public User selectUserByUserPhone(String userPhone) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		String sql = "SELECT * FROM user WHERE userPhone = ?;";
		User selectUser = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1,userPhone);
			res = ps.executeQuery();
			if(res.next()) {
				selectUser = new User();
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				selectUser.setUserId(res.getInt("userId"));
				selectUser.setUserName(res.getString("userName"));
				selectUser.setUserPass(res.getString("userPass"));
				selectUser.setUserSex(res.getString("userSex"));
				Date dt1 = null;
				try {
					dt1 = format.parse(format.format(res.getTimestamp("userBirth")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setUserBirth(dt1);
				selectUser.setUserPhone(res.getString("userPhone"));
				selectUser.setUserEmail(res.getString("userEmail"));
				selectUser.setIndividualResume(res.getString("individualResume"));
				selectUser.setUserHeadImg(res.getString("userHeadImg"));
				selectUser.setExperience(res.getInt("experience"));
				selectUser.setUserState(res.getInt("userState"));
				Date dt2 = null;
				try {
					dt2 = format.parse(format.format(res.getTimestamp("regDate")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setRegDate(dt2);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return selectUser;
	}

	@Override
	public User selectUserByUserEmail(String userEmail) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		String sql = "SELECT * FROM user WHERE userEmail = ?;";
		User selectUser = null;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1,userEmail);
			res = ps.executeQuery();
			if(res.next()) {
				selectUser = new User();
				SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				selectUser.setUserId(res.getInt("userId"));
				selectUser.setUserName(res.getString("userName"));
				selectUser.setUserPass(res.getString("userPass"));
				selectUser.setUserSex(res.getString("userSex"));
				Date dt1 = null;
				try {
					dt1 = format.parse(format.format(res.getTimestamp("userBirth")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setUserBirth(dt1);
				selectUser.setUserPhone(res.getString("userPhone"));
				selectUser.setUserEmail(res.getString("userEmail"));
				selectUser.setIndividualResume(res.getString("individualResume"));
				selectUser.setUserHeadImg(res.getString("userHeadImg"));
				selectUser.setExperience(res.getInt("experience"));
				selectUser.setUserState(res.getInt("userState"));
				Date dt2 = null;
				try {
					dt2 = format.parse(format.format(res.getTimestamp("regDate")));
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					System.out.println("转换成Date的String不是规范值");
					e.printStackTrace();
				}
				selectUser.setRegDate(dt2);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(res, ps, con);
		}
		return selectUser;
	}

	@Override
	public int insertUser(User user) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		String sql = "INSERT INTO user(userName,userPass,userSex,userBirth,userPhone,userEmail,individualResume,userHeadImg,experience,userState,regDate) VALUES (?,?,?,?,?,?,?,?,?,?,?);";
		int resultNum = 0;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			ps.setString(1,user.getUserName());
			ps.setString(2,user.getUserPass());
			ps.setString(3,user.getUserSex());
			String dt1 = format.format(user.getUserBirth().getTime());
			ps.setString(4,dt1);
			ps.setString(5,user.getUserPhone());
			ps.setString(6,user.getUserEmail());
			ps.setString(7,user.getIndividualResume());
			ps.setString(8,user.getUserHeadImg());
			ps.setInt(9,user.getExperience());
			ps.setInt(10,user.getUserState());
			String dt2 = format.format(user.getRegDate().getTime());
			ps.setString(11,dt2);
			resultNum = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(null, ps, con);
		}
		return resultNum;
	}

	@Override
	public int deleteUserByUserId(int userId) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		String sql = "DELETE FROM user WHERE userId = ?;";
		int resultNum = 0;
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,userId);
			resultNum = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(null, ps, con);
		}
		return resultNum;
	}

	@Override
	public int updateUser(User user) {
		// TODO Auto-generated method stub
		List<Object>  params = new ArrayList<Object>();
		int resultNum = 0;
		if(user.getUserId() != 0) {
			Connection con = null;
			PreparedStatement ps = null;
			StringBuffer sql = new StringBuffer("UPDATE user SET userId = ?");
			params.add(user.getUserId());
			if(user.getUserId() != 0) {
				sql.append(",userId = ?");
				params.add(user.getUserId());
			}//用户id
			if(user.getUserName()!=null && !"".equals(user.getUserName())) {
				sql.append(",userName = ?");
				params.add(user.getUserName());
			}//用户名
			if(user.getUserPass()!=null && !"".equals(user.getUserPass())) {
				sql.append(",userPass = ?");
				params.add(user.getUserPass());
			}//密码
			if(user.getUserSex()!=null && !"".equals(user.getUserSex())) {
				sql.append(",userSex = ?");
				params.add(user.getUserSex());
			}//性别
			if(user.getUserBirth()!=null) {
				sql.append(",userBirth = ?");
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String dt = format.format(user.getUserBirth().getTime());
				params.add(dt);
			}//用户生日
			if(user.getUserPhone()!=null && !"".equals(user.getUserPhone())) {
				sql.append(",userPhone = ?");
				params.add(user.getUserPhone());
			}//用户电话
			if(user.getUserEmail()!=null && !"".equals(user.getUserEmail())) {
				sql.append(",userEmail = ?");
				params.add(user.getUserEmail());
			}//用户邮箱
			if(user.getIndividualResume()!=null && !"".equals(user.getIndividualResume())) {
				sql.append(",individualResume = ?");
				params.add(user.getIndividualResume());
			}//用户签名
			if(user.getUserHeadImg()!=null && !"".equals(user.getUserHeadImg())) {
				sql.append(",userHeadImg = ?");
				params.add(user.getUserHeadImg());
			}//用户头像模糊查询
			if(user.getExperience() != 0) {
				sql.append(",experience = ?");
				params.add(user.getExperience());
			}//用户经验
			if(user.getUserState() != 0) {
				sql.append(",userState = ?");
				params.add(user.getUserState());
			}//用户状态
			sql.append(",regDate = ?");
			sql.append(" WHERE adminId = ?");
			try {
				con = BaseDao.getCon();
				ps = con.prepareStatement(sql.toString());
				for(int i =0;i<params.size();i++) {
					ps.setObject(i+1, params.get(i));
				}
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String dt = format.format(new Date().getTime());
				ps.setString(params.size()+1,dt);
				ps.setInt(params.size()+2,user.getUserId());
				resultNum = ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				BaseDao.closeAll(null, ps, con);
			}
		}
		return resultNum;
	}

}
